package com.octopus.optiworks.dao;

import org.apache.shiro.crypto.hash.Sha256Hash;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

public class BootstrapDataPopulator implements InitializingBean {

    private DataSource dataSource;

    private SessionFactory sessionFactory;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Session factory is only injected to ensure it is initialized before this runs
    @Autowired
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    public void afterPropertiesSet() throws Exception {
        //because we're using an in-memory hsqldb for the sample app, a new one will be created each time the
        //app starts, so insert the sample admin user at startup:
        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);

        int i = jdbcTemplate.queryForInt("select count(1) from employeerole where id=1");
        if(i == 1)
        	return;
        
        //jdbcTemplate.execute("set identity_insert 'employeerole' ON");
        jdbcTemplate.execute("insert into employeerole values (1, 'The default role given to all users.', 'User')");
        jdbcTemplate.execute("insert into employeerole values (2, 'The administrator role only given to site admins', 'Admin')");
        jdbcTemplate.execute("insert into employeerole values (3, 'The role can design the new process template.', 'Designer')");
        jdbcTemplate.execute("insert into employeerole values (4, 'The role create the lot and start the processes.', 'Leader')");
        jdbcTemplate.execute("insert into employeerole values (5, 'The role manage all data of product and process.', 'Manager')");
        jdbcTemplate.execute("insert into employeerole values (6, 'The role operate Step10_1.', 'Step10_1 Operator')");
        jdbcTemplate.execute("insert into employeerole values (30, 'The role operate Step10_2.', 'Step10_2 Operator')");
        jdbcTemplate.execute("insert into employeerole values (7, 'The role operate Step20.', 'Step20 Operator')");
        jdbcTemplate.execute("insert into employeerole values (8, 'The role operate Step30-1.', 'Step30-1 Operator')");
        jdbcTemplate.execute("insert into employeerole values (24, 'The role operate Step30-2.', 'Step30-2 Operator')");
        jdbcTemplate.execute("insert into employeerole values (9, 'The role operate Step40.', 'Step40 Operator')");
        jdbcTemplate.execute("insert into employeerole values (10, 'The role operate Step50.', 'Step50 Operator')");
        jdbcTemplate.execute("insert into employeerole values (11, 'The role operate Step60.', 'Step60 Operator')");
        jdbcTemplate.execute("insert into employeerole values (12, 'The role operate Step70.', 'Step70 Operator')");
        jdbcTemplate.execute("insert into employeerole values (13, 'The role operate Step80.', 'Step80 Operator')");
        jdbcTemplate.execute("insert into employeerole values (14, 'The role operate Step90.', 'Step90 Operator')");
        jdbcTemplate.execute("insert into employeerole values (15, 'The role operate Step100-1.', 'Step100-1 Operator')");
        jdbcTemplate.execute("insert into employeerole values (25, 'The role operate Step100-2.', 'Step100-2 Operator')");
        jdbcTemplate.execute("insert into employeerole values (26, 'The role operate Step100-3.', 'Step100-3 Operator')");
        jdbcTemplate.execute("insert into employeerole values (16, 'The role operate Step110.', 'Step110 Operator')");
        jdbcTemplate.execute("insert into employeerole values (17, 'The role operate Step120-1.', 'Step120-1 Operator')");
        jdbcTemplate.execute("insert into employeerole values (27, 'The role operate Step120-2.', 'Step120-2 Operator')");
        jdbcTemplate.execute("insert into employeerole values (18, 'The role operate Step130.', 'Step130 Operator')");
        jdbcTemplate.execute("insert into employeerole values (19, 'The role operate Step140-1.', 'Step140-1 Operator')");
        jdbcTemplate.execute("insert into employeerole values (28, 'The role operate Step140-2.', 'Step140-2 Operator')");
        jdbcTemplate.execute("insert into employeerole values (20, 'The role operate Step150.', 'Step150 Operator')");
        jdbcTemplate.execute("insert into employeerole values (21, 'The role operate Step160.', 'Step160 Operator')");
        jdbcTemplate.execute("insert into employeerole values (22, 'The role operate Step170-1.', 'Step170-1 Operator')");
        jdbcTemplate.execute("insert into employeerole values (29, 'The role operate Step170-2.', 'Step170-2 Operator')");
        jdbcTemplate.execute("insert into employeerole values (23, 'The role operate Step180.', 'Step180 Operator')");
        //jdbcTemplate.execute("set identity_insert 'employeerole' OFF");
        
        jdbcTemplate.execute("insert into role_permission values (1, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (2, 'user:*')");
        jdbcTemplate.execute("insert into role_permission values (2, 'role:*')");
        jdbcTemplate.execute("insert into role_permission values (2, 'template:*')");
        jdbcTemplate.execute("insert into role_permission values (2, 'spec:*')");
        jdbcTemplate.execute("insert into role_permission values (2, 'process:*')");
        jdbcTemplate.execute("insert into role_permission values (2, 'step:*')");
        jdbcTemplate.execute("insert into role_permission values (2, 'dictionary:*')");
        jdbcTemplate.execute("insert into role_permission values (3, 'template:*')");
        jdbcTemplate.execute("insert into role_permission values (3, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (4, 'process:*')");
        jdbcTemplate.execute("insert into role_permission values (4, 'step:input:*')");
        jdbcTemplate.execute("insert into role_permission values (5, 'step:*')");
        jdbcTemplate.execute("insert into role_permission values (5, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (6, 'step:input:step00')");
        jdbcTemplate.execute("insert into role_permission values (6, 'step:input:step10_1')");
        jdbcTemplate.execute("insert into role_permission values (6, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (30, 'step:input:step10_2')");
        jdbcTemplate.execute("insert into role_permission values (30, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (7, 'step:input:step20')");
        jdbcTemplate.execute("insert into role_permission values (7, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (8, 'step:input:step30_1')");
        jdbcTemplate.execute("insert into role_permission values (8, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (24, 'step:input:step30_2')");
        jdbcTemplate.execute("insert into role_permission values (24, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (9, 'step:input:step40')");
        jdbcTemplate.execute("insert into role_permission values (9, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (10, 'step:input:step50')");
        jdbcTemplate.execute("insert into role_permission values (10, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (11, 'step:input:step60')");
        jdbcTemplate.execute("insert into role_permission values (11, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (12, 'step:input:step70')");
        jdbcTemplate.execute("insert into role_permission values (12, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (13, 'step:input:step80')");
        jdbcTemplate.execute("insert into role_permission values (13, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (14, 'step:input:step90')");
        jdbcTemplate.execute("insert into role_permission values (14, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (15, 'step:input:step100_1')");
        jdbcTemplate.execute("insert into role_permission values (15, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (25, 'step:input:step100_2')");
        jdbcTemplate.execute("insert into role_permission values (25, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (26, 'step:input:step100_3')");
        jdbcTemplate.execute("insert into role_permission values (26, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (16, 'step:input:step110')");
        jdbcTemplate.execute("insert into role_permission values (16, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (17, 'step:input:step120_1')");
        jdbcTemplate.execute("insert into role_permission values (17, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (27, 'step:input:step120_2')");
        jdbcTemplate.execute("insert into role_permission values (27, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (18, 'step:input:step130')");
        jdbcTemplate.execute("insert into role_permission values (18, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (19, 'step:input:step140_1')");
        jdbcTemplate.execute("insert into role_permission values (19, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (28, 'step:input:step140_2')");
        jdbcTemplate.execute("insert into role_permission values (28, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (20, 'step:input:step150')");
        jdbcTemplate.execute("insert into role_permission values (20, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (21, 'step:input:step160')");
        jdbcTemplate.execute("insert into role_permission values (21, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (22, 'step:input:step170_1')");
        jdbcTemplate.execute("insert into role_permission values (22, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (29, 'step:input:step170_2')");
        jdbcTemplate.execute("insert into role_permission values (29, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (23, 'step:input:step180')");
        jdbcTemplate.execute("insert into role_permission values (23, 'step:input:datasheet')");
        jdbcTemplate.execute("insert into role_permission values (23, 'process:query:*')");
        jdbcTemplate.execute("insert into role_permission values (23, 'process:export:*')");
        
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (1, 'admin', 'sn-admin', 'admin@optiworks.org', '" + new Sha256Hash("admin").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (2, 'user', 'sn-user', 'user@optiworks.org', '" + new Sha256Hash("user").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (3, 'designer', 'sn-designer', 'designer@optiworks.org', '" + new Sha256Hash("designer").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (4, 'leader', 'sn-leader', 'leader@optiworks.org', '" + new Sha256Hash("leader").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (5, 'manager', 'sn-manager', 'manager@optiworks.org', '" + new Sha256Hash("manager").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (6, 'op10-1', 'sn-op10-1', 'test@optiworks.org', '" + new Sha256Hash("op10-1").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (30, 'op10-2', 'sn-op10-2', 'test@optiworks.org', '" + new Sha256Hash("op10-2").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (7, 'op20', 'sn-op20', 'test@optiworks.org', '" + new Sha256Hash("op20").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (8, 'op30-1', 'sn-op30-1', 'test@optiworks.org', '" + new Sha256Hash("op30-1").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (24, 'op30-2', 'sn-op30-2', 'test@optiworks.org', '" + new Sha256Hash("op30-2").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (9, 'op40', 'sn-op40', 'test@optiworks.org', '" + new Sha256Hash("op40").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (10, 'op50', 'sn-op50', 'test@optiworks.org', '" + new Sha256Hash("op50").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (11, 'op60', 'sn-op60', 'test@optiworks.org', '" + new Sha256Hash("op60").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (12, 'op70', 'sn-op70', 'test@optiworks.org', '" + new Sha256Hash("op70").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (13, 'op80', 'sn-op80', 'test@optiworks.org', '" + new Sha256Hash("op80").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (14, 'op90', 'sn-op90', 'test@optiworks.org', '" + new Sha256Hash("op90").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (15, 'op100-1', 'sn-op-100-1', 'test@optiworks.org', '" + new Sha256Hash("op100-1").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (25, 'op100-2', 'sn-op-100-2', 'test@optiworks.org', '" + new Sha256Hash("op100-2").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (26, 'op100-3', 'sn-op-100-3', 'test@optiworks.org', '" + new Sha256Hash("op100-3").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (16, 'op110', 'sn-op110', 'test@optiworks.org', '" + new Sha256Hash("op110").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (17, 'op120-1', 'sn-op120-1', 'test@optiworks.org', '" + new Sha256Hash("op120-1").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (27, 'op120-2', 'sn-op120-2', 'test@optiworks.org', '" + new Sha256Hash("op120-2").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (18, 'op130', 'sn-op130', 'test@optiworks.org', '" + new Sha256Hash("op130").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (19, 'op140-1', 'sn-op140-1', 'test@optiworks.org', '" + new Sha256Hash("op140-1").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (28, 'op140-2', 'sn-op140-2', 'test@optiworks.org', '" + new Sha256Hash("op140-2").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (20, 'op150', 'sn-op150', 'test@optiworks.org', '" + new Sha256Hash("op150").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (21, 'op160', 'sn-op160', 'test@optiworks.org', '" + new Sha256Hash("op160").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (22, 'op170-1', 'sn-op170-1', 'test@optiworks.org', '" + new Sha256Hash("op170-1").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (29, 'op170-2', 'sn-op170-2', 'test@optiworks.org', '" + new Sha256Hash("op170-2").toHex() + "')");
        jdbcTemplate.execute("insert into employee(id,username,serial,email,password) values (23, 'op180', 'sn-op180', 'test@optiworks.org', '" + new Sha256Hash("op180").toHex() + "')");
        
        jdbcTemplate.execute("insert into user_role values (1, 1)");
        jdbcTemplate.execute("insert into user_role values (1, 2)");
        jdbcTemplate.execute("insert into user_role values (1, 3)");
        jdbcTemplate.execute("insert into user_role values (1, 4)");
        jdbcTemplate.execute("insert into user_role values (1, 5)");
        jdbcTemplate.execute("insert into user_role values (1, 6)");
        jdbcTemplate.execute("insert into user_role values (2, 1)");
        jdbcTemplate.execute("insert into user_role values (3, 1)");
        jdbcTemplate.execute("insert into user_role values (3, 3)");
        jdbcTemplate.execute("insert into user_role values (4, 1)");
        jdbcTemplate.execute("insert into user_role values (4, 4)");
        jdbcTemplate.execute("insert into user_role values (5, 1)");
        jdbcTemplate.execute("insert into user_role values (5, 2)");
        jdbcTemplate.execute("insert into user_role values (5, 5)");
        jdbcTemplate.execute("insert into user_role values (6, 1)");
        jdbcTemplate.execute("insert into user_role values (6, 6)");
        jdbcTemplate.execute("insert into user_role values (30, 1)");
        jdbcTemplate.execute("insert into user_role values (30, 30)");
        jdbcTemplate.execute("insert into user_role values (7, 1)");
        jdbcTemplate.execute("insert into user_role values (7, 7)");
        jdbcTemplate.execute("insert into user_role values (8, 1)");
        jdbcTemplate.execute("insert into user_role values (8, 8)");
        jdbcTemplate.execute("insert into user_role values (9, 1)");
        jdbcTemplate.execute("insert into user_role values (9, 9)");
        jdbcTemplate.execute("insert into user_role values (10, 1)");
        jdbcTemplate.execute("insert into user_role values (10, 10)");
        jdbcTemplate.execute("insert into user_role values (11, 1)");
        jdbcTemplate.execute("insert into user_role values (11, 11)");
        jdbcTemplate.execute("insert into user_role values (12, 1)");
        jdbcTemplate.execute("insert into user_role values (12, 12)");
        jdbcTemplate.execute("insert into user_role values (13, 1)");
        jdbcTemplate.execute("insert into user_role values (13, 13)");
        jdbcTemplate.execute("insert into user_role values (14, 1)");
        jdbcTemplate.execute("insert into user_role values (14, 14)");
        jdbcTemplate.execute("insert into user_role values (15, 1)");
        jdbcTemplate.execute("insert into user_role values (15, 15)");
        jdbcTemplate.execute("insert into user_role values (16, 1)");
        jdbcTemplate.execute("insert into user_role values (16, 16)");
        jdbcTemplate.execute("insert into user_role values (17, 1)");
        jdbcTemplate.execute("insert into user_role values (17, 17)");
        jdbcTemplate.execute("insert into user_role values (18, 1)");
        jdbcTemplate.execute("insert into user_role values (18, 18)");
        jdbcTemplate.execute("insert into user_role values (19, 1)");
        jdbcTemplate.execute("insert into user_role values (19, 19)");
        jdbcTemplate.execute("insert into user_role values (20, 1)");
        jdbcTemplate.execute("insert into user_role values (20, 20)");
        jdbcTemplate.execute("insert into user_role values (21, 1)");
        jdbcTemplate.execute("insert into user_role values (21, 21)");
        jdbcTemplate.execute("insert into user_role values (22, 1)");
        jdbcTemplate.execute("insert into user_role values (22, 22)");
        jdbcTemplate.execute("insert into user_role values (23, 1)");
        jdbcTemplate.execute("insert into user_role values (23, 23)");
        jdbcTemplate.execute("insert into user_role values (24, 1)");
        jdbcTemplate.execute("insert into user_role values (24, 24)");
        jdbcTemplate.execute("insert into user_role values (25, 1)");
        jdbcTemplate.execute("insert into user_role values (25, 25)");
        jdbcTemplate.execute("insert into user_role values (26, 1)");
        jdbcTemplate.execute("insert into user_role values (26, 26)");
        jdbcTemplate.execute("insert into user_role values (27, 1)");
        jdbcTemplate.execute("insert into user_role values (27, 27)");
        jdbcTemplate.execute("insert into user_role values (28, 1)");
        jdbcTemplate.execute("insert into user_role values (28, 28)");
        jdbcTemplate.execute("insert into user_role values (29, 1)");
        jdbcTemplate.execute("insert into user_role values (29, 29)");

        jdbcTemplate.execute("insert into template values (1, 'The default process template of system.', 'Default', 'step10_1, step10_2, step20, step30_1, step30_2, step40, step50, step60, step70, step80," +
        		" step90, step100_1, step100_2, step100_3, step110, step120_1, step120_2, step130, step140_1, step140_2, step150, step160, step170_1, step170_2, step180')");

        jdbcTemplate.execute("insert into product values (-1,'','','','','','','','','','','',-1,false,null,'',1)");
        
    }
}
